import xlrd3
import pathlib
import sys
import sqlite3
from fractions import Fraction
import easygui as eg
import config
from decimal import Decimal
from config import log
from config import runsql,runsqlmany
import re

root=pathlib.Path(__file__).parent
db=sqlite3.connect(str(root/"statistics.db"))
cur = db.cursor()
  
def initDB():
    try:
        cur.execute("DROP TABLE IF EXISTS source;")
        cur.execute("DROP TABLE IF EXISTS result_1;")
    except sqlite3.Error as e:
        log.error(f"{e}\nsource,result_1表删除失败")
        config._exit()
        
    else:
        log.info("source,result_1表删除成功")
    try:
        sql='''
            CREATE TABLE  "source" (
            "学籍号" INTEGER NOT NULL,
            "区域" TEXT,
            "学校" TEXT,
            "年级" TEXT,
            "班级" TEXT,
            "姓名" TEXT,
            
            
'''
        for i in config.colnames[5:]:
            sql+=f"\"{i}\" real,"
        sql+="PRIMARY KEY (\"学籍号\") );"
        cur.execute(sql)
    except sqlite3.Error as e:
        log.error(f"{e}\nsource表创建失败")
        config._exit("发生错误",15)
    else:
        log.info("source表创建成功")

    db.commit()

def openxls():
    filepath=eg.fileopenbox()
    log.info(f"选择文件：{filepath}")
    
    # filepath=r"D:\git\python\County-wide_statistics\学籍成绩合并.xlsx"
    wb = xlrd3.open_workbook(str(filepath))
    tempnames=wb.sheet_names()
    if(len(tempnames)==1):
        wb_active=wb.sheet_by_index(0) #设置第一个表为活动表
    else:
        tempstr="请选择录入的表：\n"
        for i in  tempnames:
            tempstr+=f"{tempnames.index(i)+1}、{i} \n"
        tempstr+="0、退出\n"
        temps=int(input(tempstr))
        if(temps==0):
            sys.exit()
        else:
            wb_active=wb.sheet_by_index(temps-1)
    return wb_active
def inputDB():

    wb_active=openxls()
    log.info(f"录入表为:<{wb_active.name}>")
    wb_row=wb_active.nrows
    log.debug(f"总计{wb_row}行")
    try:
        wbTitle=wb_active.row_values(config.titleRow)
    except Exception as e:
        log.error(f"读取表头失败：{e}")
        config._exit("发生错误",15)
    wbTitleIndex=[]
    datas=[]
    for i in range(0,len(wbTitle)):
        if(wbTitle[i]=="姓名"):
            nametitlecols=i
        if(wbTitle[i]=="学校"):
            schoolTitleCols =i
        if(wbTitle[i] in config.colnames):
            wbTitleIndex.append((i,wbTitle[i]))
        elif(wbTitle[i] in list(config.subject_alias.keys())):
            wbTitleIndex.append((i,config.subject_alias[wbTitle[i]]))

    log.debug(wbTitleIndex)
    sheetcolnamesList=[]
    for i in wbTitleIndex:
        sheetcolnamesList.append(i[1])
    log.debug(sheetcolnamesList)
    for i in config.colnames:
        if  i not in sheetcolnamesList :
            log.error(f"找不到<{i}>列")
            config._exit("发生错误",15)

    for i in range(config.titleRow+1,wb_row):
    # for i in range(config.titleRow+1,100):
        rows=wb_active.row_values(i)
        if(rows[nametitlecols]==""):
            log.warning(f"第{i+1}行数据<{wbTitle[nametitlecols]}>为空")
        else:
            dt=[]
            for n in wbTitleIndex:
                tempCheckData=checkData(n[1],rows[n[0]])
                if(tempCheckData[0]):
                    dt.append(tempCheckData[1])
                else:
                    log.error(f"第{i+1}行<{n[1]}>"+tempCheckData[1])
                    config._exit("发生错误",15)
            schoolzone=rows[schoolTitleCols]
            if(schoolzone not in config.zone.keys()):
                log.error(f"{rows[schoolTitleCols]} 匹配不到所属片区")
                config._exit(f"校名<{schoolzone}>不在列表中",15)
            else:
                dt.append(config.zone[schoolzone])
            datas.append(dt)
            log.debug(dt)
    sheetcolnamesList.append("区域")
    placeholders = ", ".join(["?"] * len(sheetcolnamesList))
    columns_str = ", ".join(sheetcolnamesList)
    sql = f"INSERT INTO source ({columns_str}) VALUES ({placeholders})"
    log.debug(sql)
    try:
        cur.executemany(sql,datas)
        db.commit()
        log.info(f"成功插入 {len(datas)} 条数据到表")
    except sqlite3.Error as e:
        log.error(f"插入失败：{e}")
        db.rollback()
        config._exit("发生错误",15)
    return (wbTitleIndex,datas)
def showDB():
    sql='''
SELECT
source."学校",
source."年级",
Count(source."姓名") as "总计"
FROM
source
GROUP BY
source."学校",
CASE
	WHEN source."年级" = "七年级" THEN 7
	WHEN source."年级" = "八年级" THEN 8
	WHEN source."年级" = "九年级" THEN 9
	ELSE 99
END
'''
    
    result=cur.execute(sql)
    result=result.fetchall()
    for i in result:
        print(f"{i[0]} {i[1]} \t {i[2]}人")

def is_digits(s,len):
    """
    判断输入字符串是否为14位数字
    :param s: 待检查的字符串
    :return: 是14位数字返回True,否则返回False
    """
    # 正则模式：^表示开头，$表示结尾，\d{14}表示14位数字
    pattern = rf'^\d{{{len}}}$'
    return bool(re.fullmatch(pattern, str(s)) is not None)
def checkData(subject,value):
    if(subject=="学籍号"):
        if(not is_digits(value,config.studentIDLen)):
            return (False,f"学籍号<{value}>有误")
    if(subject=="班级"):
        try:
            value=str(int(value))+"班"
        except :
            pass
    if(subject in config.colnames[5:]):
        if(value=="" or value=="缺考"):
            value=config.MissingExamination
        else:
            try:
                value=float(value)
            except :
                return (False,"不是数字")
            if(float(value)>config.CheckMaxPoint):
                return (False,f"数据超过{config.CheckMaxPoint}")
    return (True,value)
def delDB():
    sql='''
SELECT
source."学校",
Count(source."姓名") as "总计"
FROM
source
GROUP BY
source."学校"
'''
    result=cur.execute(sql)
    result=result.fetchall()
    for i in result:
        print(f"{result.index(i)+1}、{i[0]} {i[1]}人")
    s=input()
    if (s=="" ):
        s=-1
    else:
        try:
            s=int(s)-1
        except:
            log.warning("输入错误")
            s=99
    if(s==-1):
        return False
    if(s==-2):
        deldbstep1(-1,result)
    else:
        deldbstep1(s,result)

def deldbstep1(index,r):

    s=input(f"确认删除 {r[index][0]} {r[index][1]} 条数据(y/n)?")
    if(s=="y" or s=="Y"):
        sql=f"DELETE FROM source WHERE (`学校`='{r[index][0]}');"
        try:
            cur.execute(sql)
            db.commit()
            log.info(f"删除 {r[index][0]} {r[index][1]} 条数据")
        except Exception as e:
            log.error(f"删除失败：{e}")
            db.rollback()
    else:
        log.info("撤消删除操作")

def inputHistorical():


    # filepath=r"D:\git\python\County-wide_statistics\入学成绩.xlsx"
    # wb = xlrd3.open_workbook(str(filepath))
    # wb_active=wb.sheet_by_index(0) #设置第一个表为活动表

    wb_active=openxls()
    wb_row=wb_active.nrows
    log.debug(f"总计{wb_row}行")
    datatemp=[]
    if(wb_row<1):
        log.error("找不到数据")
    else:
        for i in range(1,wb_row):
            rowValue=wb_active.row_values(i)
            datatemp.append(rowValue)
    print("{0:<10s}{1:<10s}\t{2}".format("入学年份","学校","平均分"))
    for i in datatemp:
        if i[1] not in list(config.zone.keys()):
            log.error(f"学校<{i[1]}>不在列表")
            config._exit(f"学校<{i[1]}>不在列表",5)
        print(f"{i[0]:<10}{i[1]:.10s}\t{i[2]:<10}")
    print("{0:<10}{1:<10s}\t{2}".format("入学年份","学校","平均分"))
    
    select_=input("1、录入\n0、返回\n")
    if(select_ != 1 and select_!='1'):
        return False
    
    result_=runsql("DROP TABLE IF EXISTS HistoricalScores")
    result_=runsql( 'CREATE TABLE "HistoricalScores" ( "id"  INTEGER PRIMARY KEY  NOT NULL, "year"  INTEGER, "school"  TEXT, "averageScore"  REAL ,"ranking" INTEGER) ;')
    if(result_ is False ):
        log.error("HistoricalScores表创建失败")
        config._exit("HistoricalScores表创建失败",10)
    result_=runsqlmany('INSERT INTO "HistoricalScores" ("year", "school", "averageScore") VALUES (?, ?, ?)',datatemp)
    if(result_ is False ):
        log.error("入学成绩录入失败")
        config._exit("入学成绩录入失败",10)
    else:
        log.info("入学成绩已录入")
    yearlist=[]
    result_=runsql('SELECT year FROM HistoricalScores GROUP BY year;')
    result_=result_.fetchall()
    for i in result_:
        sql=f'SELECT id, averageScore FROM HistoricalScores WHERE year = {i[0]} ORDer BY averageScore DESC'
        result_1=runsql(sql)
        result_1=result_1.fetchall()
        rankdata=[]
        n=1
        for j in result_1:
            rankdata.append((n,j[0]))
            n+=1
        sql=f'UPDATE "HistoricalScores" SET "ranking"=? WHERE ("id"=?)'
        result_1=runsqlmany(sql,rankdata)
        if (result_1 is False):
            log.error(f"入学成绩排名出错")
            config._exit(f"入学成绩排名出错",10)
        else:
            log.info(f"完成{i[0]}年入学成绩排名")



if __name__ =="__main__":

    print(is_digits("12345678901234",14))
    while(1):
        s=input(
"""
1、录入数据
2、清空数据(初始化数据库)
3、显示录入
4、删除数据
5、录入入学平均分
0、退出
"""
                )
        if (s=="" ):
            s=99
        else:
            try:
                s=int(s)
            except:
                log.warning("输入错误")
                s=99
        if s==1:
            inputDB()
        elif s==2 :
            initDB()
        elif s==3 :
            showDB()
        elif s==4 :
            delDB()        
        elif s==5 :
            inputHistorical()
        elif s==0 :
            sys.exit()